package com.ecube.sql;

import com.ecube.mqtt.mysql.Query;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Random;

public class Sql1562 {
    static Connection con;
    static Statement sql;
    static int res;
    public static void main(String[] args) {
        String it = "20250224";
        String F2 = "HDZ20240807001";
        String F1 = "760049";
        String F15 = "2025-02-24";
        String it1 = "20250217";
        String F21 = "HDZ20240807001";
        String F11 = "760049";
        String F151 = "2025-02-17";
        executeSql(it, F1, F2, F15, it1, F11, F21, F151);
    }


    public static void executeSql(String it,
                                  String F1,
                                  String F2,
                                  String F15,
                                  String it1,
                                  String F11,
                                  String F21,
                                  String F151
                                  ) {
        for (int i = 0; i < 24; i++) {

            String F3 = "";
            String F = "";
            for (int j = 0; j < 4; j++) {
                if (j == 0) {
                    F = "00";
                }else if (j == 1) {
                    F = "15";
                }else if (j == 2) {
                    F = "30";
                }else if (j == 3) {
                    F = "45";
                }
                if (i<10) {
                    F3 = "0" + i;
                }else {
                    F3 = String.valueOf(i);
                }
                Date date = new Date();
                String F4 = it  + F3 + F;
                String F41 = it1  + F3 + F;
                SimpleDateFormat dateFormat1 = new SimpleDateFormat("yyyyMMdd");
                String F10 = dateFormat1.format(date);
                String ct = F10;
                Random random = new Random();
                // 生成一个随机数，范围在0（包含）到1000（不包含）之间
                double minimum = 200;
                double maximum = 400;
                double randomNumber = minimum + (double) (Math.random() * maximum);
                // 格式化为两位小数
                String F8 = String.format("%.2f", randomNumber);
                double minimum1 = 400;
                double maximum1 = 500;
                double randomNumber1 = minimum1 + (double) (Math.random() * maximum1);
                // 格式化为两位小数
                String F9 = String.format("%.2f", randomNumber1);
                getChargeSnno("UAT", F1, F2, F4, F15, F11, F21, F41, F151);
                System.out.println(F1 + "站" + F4 + "时段写入成功");
            }

        }
    }
    public static void getChargeSnno(String env,
                                       String F1,
                                       String F2,
                                       String F4,
                                     String F15,
                                       String F11,
                                       String F21,
                                       String F41,
                                     String F151) {
        Query c = new Query();
        con = c.getConnection(env, "cwp_ads");
        try {
            sql = con.createStatement();        // 实例化Statement对象
            String sqlexe = "update\n" +
                    "\tcwp_ads.ads_zk1562\n" +
                    "set\n" +
                    "\tF4 = '" + F4 + "',\n" +
                    "\tF1 = '" + F1 + "',\n" +
                    "\tF2 = '" + F2 + "',\n" +
                    "\tF15 = '" + F15 + "'\n" +
                    "where\n" +
                    "\tF4 = '" + F41 + "'\n" +
                    "\tand F1 = '" + F11 + "'\n" +
                    "\tand F2 = '" + F21 + "'\n" +
                    "\tand F15 = '" + F151 + "'";
            System.out.println(sqlexe);
            res = sql.executeUpdate(sqlexe);        // 执行SQL语句，返回结果集
            System.out.println("执行" + res + "条成功");
//            while (res.next()) {
//                snno = res.getString("snno");
//                System.out.print(" snno:" + snno);
//            }
            //System.out.println(sqlexe);
            con.close();
        } catch (Exception e) {
            e.printStackTrace();
        }

    }
    public Connection getConnection(String env, String lib) {
        try {
            // 加载数据库驱动类
            Class.forName("com.mysql.jdbc.Driver");
            //Class.forName("com.mysql.cj.jdbc.Driver");
            // 连接数据库
            // 注意此处因为数据库和系统时区差异会引起异常所以在在jdbc连接的url后面加上
            String url = "";
            if (env.equals("UAT")) {
                url = "jdbc:mysql://pc-uf6j29qa2ez19opwm.rwlb.rds.aliyuncs.com:3306/" + lib;
                con = DriverManager.getConnection(url
                        + "?serverTimezone=UTC&characterEncoding=utf-8", "rhdk", "Rhdk@2019");
            } else if (env.equals("FAT")) {
                url = "jdbc:mysql://rm-uf6kl4uuc01g3018i123930.mysql.rds.aliyuncs.com:3306/" + lib;
                con = DriverManager.getConnection(url
                        + "?serverTimezone=UTC&characterEncoding=utf-8", "rhdk", "Rhdk@2019");
            }else if (env.equals("DEV")) {
                url = "jdbc:mysql://10.6.6.73:3306/" + lib;
                con = DriverManager.getConnection(url
                        + "?useSSL=false&serverTimezone=UTC&characterEncoding=utf-8", "root", "rhzl@2014");

                System.out.println(url);
            }


        } catch (Exception e) {
            System.out.println("连接数据库失败");
            System.out.println(e.getMessage());
            e.printStackTrace();
        }
        return con;
    }
}
